package businesslogic;


import java.io.IOException;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Normalizer;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.XPathExpressionException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import businesslogic.EntityInfo.SentimentType;

import com.alchemyapi.api.AlchemyAPI;


public class Util {
	
	   public static SentimentType getSentimentType(String sentimentType) {
			if(sentimentType.equals("positive")){
				return SentimentType.POSITIVE;
			}
			else if(sentimentType.equals("negative")){
				return SentimentType.NEGATIVE;
			}
			else if(sentimentType.equals("neutral")){
				return SentimentType.NEUTRAL;
			}
			else{
				return null;
			}
		}
	   
	    public static String getStringFromDocument(Document doc) {
	        try {
	            DOMSource domSource = new DOMSource(doc);
	            StringWriter writer = new StringWriter();
	            StreamResult result = new StreamResult(writer);

	            TransformerFactory tf = TransformerFactory.newInstance();
	            Transformer transformer = tf.newTransformer();
	            transformer.transform(domSource, result);

	            return writer.toString();
	        } catch (TransformerException ex) {
	            ex.printStackTrace();
	            return null;
	        }
	    }
	    
		public static String getTagValue(String sTag, Element eElement) {
			NodeList nlList = eElement.getElementsByTagName(sTag);
			if(nlList.getLength() > 0){
				Node aNode = nlList.item(0);
				return aNode.getTextContent();
			}
			return null;
		}
		
		public static String getMainTextFromURL(AlchemyAPI alchemyObj, String url)
				throws SAXException, ParserConfigurationException,
				XPathExpressionException {
			Document doc = null;
			String articleText = "";
			try{
				doc = alchemyObj.URLGetText(url);
				NodeList nl = doc.getElementsByTagName("results");
				Element entityElement = (Element)nl.item(0);
				articleText = Util.getTagValue("text", entityElement);
				articleText = Normalizer.normalize(articleText, Normalizer.Form.NFC);
				articleText = articleText.replaceAll("\t", "");
				articleText = articleText.replaceAll(" ++", " ");
				articleText = articleText.replaceAll("\n+", "\n");
			}
			catch(IOException err) {
				System.out.println(err);
			}
			return articleText;
		}
		
    	public static List<String> getTalkbackFromHaaretz(String htmlPage) {
    		List<String> talkbacks = new ArrayList<String>();
    		String patternStr = "<li class=\" mainComment\"(.*?)>" +
					"(.*?)<a href=\"#\"(.*?)>(.*?)</a>" +
					"(.*?)<p>(.*?)</p>(.*?)</li>";
			// Compile and use regular expression
			Pattern pattern = Pattern.compile(patternStr, Pattern.DOTALL);
			Matcher matcher;
			matcher = pattern.matcher(htmlPage);
			while (matcher.find()) {
				String comment = matcher.group(4) + matcher.group(6); 
				talkbacks.add(comment);
			}
    		return talkbacks;
    	}
    	
    	public static void createAnalyzedPage(Document doc, String urlAddress, String entityType, String text, List<String> talkbacks) {
    		
    		Calendar cal = Calendar.getInstance();
    		Date date = cal.getTime();
    		
            if(checkIfPageWasVisited(urlAddress)){
            	return;
            }
    		
    		int articleID = writeToArticleTable(urlAddress, text, date);
    	
    		List<Integer> talkbackIDList = writeToTalkbacksTable(talkbacks, articleID);
    		
    		//analyze the main article
    		NodeList nList = doc.getElementsByTagName("type");
    		for (int temp = 0; temp < nList.getLength(); ++temp) {
    			Node nNode = nList.item(temp);
    			if (nNode.getNodeType() == Node.ELEMENT_NODE && nNode.getParentNode().getNodeName() == "entity") {
    				if(nNode.getFirstChild().getNodeValue().equalsIgnoreCase(entityType)){
    					Element entityElement = (Element) nNode.getParentNode();
    					String entityName = Util.getTagValue("text", entityElement);
    					String relevance = Util.getTagValue("relevance", entityElement);
    					Double relevanceDouble = Double.parseDouble(relevance);
    					Element sentimentElement = (Element) entityElement.getElementsByTagName("sentiment").item(0);
    					String sentimentType = Util.getTagValue("type", sentimentElement);
    					SentimentType sentimentTypeEnum = Util.getSentimentType(sentimentType); 
    					String sentimentScore = Util.getTagValue("score", sentimentElement);
    					Double sentimentScoreDouble = 0.0;
    					if(sentimentScore != null){
    						sentimentScoreDouble = Double.parseDouble(sentimentScore);
    					}
    					System.out.println("The data:");
    					System.out.println("Entity Name: " + entityName);
    					System.out.println("sentiment Score: " + sentimentScoreDouble);
    					System.out.println("Relevance: " + relevanceDouble);
    					System.out.println("Article ID: " + articleID);
    					//saving the data into the DB
    					writeToEntitiesTable(entityName, sentimentScoreDouble, relevanceDouble, -1, articleID);
    				}
    			}
    		}
    		
    		//Analyze the talkbacks and enter them to the DB
    		int index = 0;
    		for(String talkback : talkbacks){
    			int talkbackID = talkbackIDList.get(index);
    			index++;
    			Document talkdoc = null;
    			try {
    				talkdoc = GSAT.alchemyObj.TextGetRankedNamedEntities(talkback, GSAT.entityParams);
				} catch (XPathExpressionException | IOException | SAXException
						| ParserConfigurationException e) {
					e.printStackTrace();
				}
    			System.out.println(talkback);
    			System.out.println(Util.getStringFromDocument(talkdoc));
    			NodeList nList2 = talkdoc.getElementsByTagName("type");
        		for (int temp = 0; temp < nList2.getLength(); ++temp) {
        			Node nNode = nList2.item(temp);
        			if (nNode.getNodeType() == Node.ELEMENT_NODE && nNode.getParentNode().getNodeName() == "entity") {
        				if(nNode.getFirstChild().getNodeValue().equalsIgnoreCase(entityType)){
        					Element entityElement = (Element) nNode.getParentNode();
        					String entityName = Util.getTagValue("text", entityElement);
        					String relevance = Util.getTagValue("relevance", entityElement);
        					Double relevanceDouble = Double.parseDouble(relevance);
        					Element sentimentElement = (Element) entityElement.getElementsByTagName("sentiment").item(0);
        					String sentimentType = Util.getTagValue("type", sentimentElement);
        					SentimentType sentimentTypeEnum = Util.getSentimentType(sentimentType); 
        					String sentimentScore = Util.getTagValue("score", sentimentElement);
        					Double sentimentScoreDouble = 0.0;
        					if(sentimentScore != null){
        						sentimentScoreDouble = Double.parseDouble(sentimentScore);
        					}
        					System.out.println("The data:");
        					System.out.println("Entity Name: " + entityName);
        					System.out.println("sentiment Score: " + sentimentScoreDouble);
        					System.out.println("Relevance: " + relevanceDouble);
        					System.out.println("Article ID: " + articleID);
        					//saving the data into the DB
        					writeToEntitiesTable(entityName, sentimentScoreDouble, relevanceDouble, talkbackID, articleID);
        				}
        			}
        		}
    		}
    	}

		private static List<Integer> writeToTalkbacksTable(List<String> talkbacks, int articleID) {
			Connection conn = null;
			java.sql.PreparedStatement stmt = null;
			
			List<Integer> idList = new ArrayList<Integer>(); 
			
			Random randomGenerator = new Random();
            int randomInt = 0;
			
			for(String tb : talkbacks){
				try {
					conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
					conn.setAutoCommit(false);
//			        int rset = 0;
			        String dbQuery = "INSERT INTO gsat.Talkbacks(ID, ArticleID, TalkbackText) VALUES(?,?,?)";
			        stmt = conn.prepareStatement(dbQuery);
			        
			        randomInt = randomGenerator.nextInt(10000000);
			        idList.add(randomInt);
		            stmt.setInt(1, randomInt);
		            
		            stmt.setInt(2, articleID);
		            
		            java.sql.Clob clob =  conn.createClob();
		            clob.setString(1, tb);
		            stmt.setClob(3, clob);
		            
//					rset = stmt.executeUpdate();
					stmt.executeUpdate();
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
				}
	            try { if (stmt != null) stmt.close(); } catch(Exception e) { }
	            try { if (conn != null) conn.close(); } catch(Exception e) { }
			}
			return idList;
		}

		private static int writeToArticleTable(String urlAddress, String text, Date date) {
			
			//choose a random article ID that doesnt exist in the DB
			Random randomGenerator = new Random();
            int randomInt = randomGenerator.nextInt(10000000);
            
//            while(checkIfArticleIdExists(randomInt)){
//            	randomInt = randomGenerator.nextInt(10000000);
//            }
            
			
			Connection conn = null;
			java.sql.PreparedStatement stmt = null;
			
			java.util.Date dt = new java.util.Date();
			java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String currentTime = sdf.format(dt);
			
			try {
				conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
				conn.setAutoCommit(false);
//		        int rset = 0;
		        String dbQuery = "INSERT INTO gsat.Article(ID, URL, MainText, DateAdded) VALUES(?,?,?,?)";
		        stmt = conn.prepareStatement(dbQuery);
	            stmt.setInt(1, randomInt);
	            stmt.setString(2, urlAddress);
	            
	            java.sql.Clob clob =  conn.createClob();
	            clob.setString(1, text);
	            
	            stmt.setClob(3, clob);
	            stmt.setString(4, currentTime);
	            
//				rset = stmt.executeUpdate();
				stmt.executeUpdate();
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
            try { if (stmt != null) stmt.close(); } catch(Exception e) { }
            try { if (conn != null) conn.close(); } catch(Exception e) { }
			return randomInt;
		}

		private static boolean checkIfPageWasVisited(String urlAddress) {
			Connection conn = null;
			java.sql.PreparedStatement stmt = null;
			try {
				conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
				ResultSet rset;
		        String dbQuery = "SELECT * FROM gsat.article WHERE URL = ?;";
		        stmt = conn.prepareStatement(dbQuery);
		        stmt.setString(1, urlAddress);
		        rset = stmt.executeQuery();
		        if(rset.next()){
		        	System.out.println("PAGE ALREADY VISITED");
		        	return true;
		        }
		        return false;
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
            try { if (stmt != null) stmt.close(); } catch(Exception e) { }
            try { if (conn != null) conn.close(); } catch(Exception e) { }
			return false;
			
		}

		private static boolean checkIfArticleIdExists(int randomInt) {
			Connection conn = null;
			java.sql.PreparedStatement stmt = null;
			ResultSet rset = null;
			try {
				conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
		        String dbQuery = "SELECT * FROM gsat.article WHERE ID = ?;";
		        stmt = conn.prepareStatement(dbQuery);
		        stmt.setInt(1, randomInt);
		        rset = stmt.executeQuery();
		        if(rset.next()){
		        	return true;
		        }
		        return false;
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
            try { if (stmt != null) stmt.close(); } catch(Exception e) { }
            try { if (conn != null) conn.close(); } catch(Exception e) { }
            try { if (rset != null) rset.close(); } catch(Exception e) { }
			return false;
		}

		private static void writeToEntitiesTable(String entityName, Double sentimentScoreDouble, Double relevance, int talkbackID, int articleID) {
			Connection conn = null;
			java.sql.PreparedStatement stmt = null;
			try {
				conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
				conn.setAutoCommit(false);
		        String dbQuery = "INSERT INTO gsat.entities(Name,SentimentScore, Relevance, TalkbackID, ArticleID) VALUES(?,?,?,?,?)";
		        stmt = conn.prepareStatement(dbQuery);
	            stmt.setString(1, entityName);
	            stmt.setDouble(2, sentimentScoreDouble);
	            stmt.setDouble(3, relevance);
	            stmt.setInt(4, talkbackID);
	            stmt.setInt(5, articleID);
				stmt.executeUpdate();
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
            try { if (stmt != null) stmt.close(); } catch(Exception e) { }
            try { if (conn != null) conn.close(); } catch(Exception e) { }
            
		}
}
